package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.YiZhengHomeApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.core.domain.QuotaManage;
import com.dhcc.DRGsHosp.infra.util.ConstantEnum;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**
 * @title		:YbHomeApplicationImpl.java
 * @description	:
 * @author		:lileizheng
 * @date		:2015年4月29日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class YiZhengHomeApplicationImpl implements YiZhengHomeApplication {
	
	private QueryChannelService queryChannel;

    private QueryChannelService getQueryChannelService(){
       if(queryChannel==null){
          queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
       }
     return queryChannel;
    }
   /*   定义DRGs数据源*/
	private QueryChannelService queryChannel_DRGs;

    private QueryChannelService getQueryChannelDRGsService(){
       if(queryChannel_DRGs==null){
    	   queryChannel_DRGs = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel_DRGs");
       }
     return queryChannel_DRGs;
    }
    
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj; 

	@Override
	public Map<String, Object> getTotalAnalysis(String s, String e,String stdId) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select count(1) allcount, sum(over60) over60count, \n ");
		sqlStr.append(" count(1)-sum(over60)-sum(decode(ingroup,1,1,0)) notingroupcount,sum(decode(ingroup,1,1,0)) ingroupcount \n ");
		sqlStr.append(" from ( \n ");
		sqlStr.append(" select case when t.in_days>60 then 1 else 0 end over60, \n ");
		sqlStr.append(" case when pardrg_code is not null and t.in_days < 61 then 1 else 0 end ingroup, \n ");
		sqlStr.append(" d.* from drgs.T_MEDICALRECORD t \n ");
		sqlStr.append(" left join drgs.t_patientdrg d \n ");
		sqlStr.append(" on t.id=d.parid \n ");
		sqlStr.append(" where 1=1 ");
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		sqlStr.append(" ) \n ");
		List<Object[]> list = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		resultMap.put("common", list);
		
		sqlStr = new StringBuilder();
		sqlStr.append(" select  \n ");
		sqlStr.append(" sum(dead) deadtotal,sum(dead1) dead1total,sum(dead2) dead2total \n ");
		sqlStr.append(" from( \n ");
		sqlStr.append(" select  \n ");
		sqlStr.append(" case when out_way=5 then 1 else 0 end dead, \n ");
		sqlStr.append(" case when out_way=5 and as_risk_level=1 then 1 else 0 end dead1, \n ");
		sqlStr.append(" case when out_way=5 and as_risk_level=2 then 1 else 0 end dead2 \n ");
		sqlStr.append("  from DRGS.T_MEDICALRECORD t \n ");
		sqlStr.append(" left join DRGS.t_Patientdrg p \n ");
		sqlStr.append(" on t.id=p.parid \n ");
		sqlStr.append(" left join DRGS.T_ANNUALSTD a \n ");
		sqlStr.append(" on a.as_drg_code=p.pardrg_code \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		
		sqlStr.append(" and as_id='"+stdId+"') \n ");
		List<Object[]> listdead = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		resultMap.put("dead", listdead);
		return resultMap;
	}

	@Override
	public List<Object[]> getDisCaseInfo(String s, String e) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select t.*,rownum rowindex from ( \n ");
		sqlStr.append(" select mdc,mdc_desc,count(1) casecount from( \n ");
		sqlStr.append(" select  \n ");
		sqlStr.append(" d.* from drgs.T_MEDICALRECORD t \n ");
		sqlStr.append(" left join drgs.t_patientdrg d \n ");
		sqlStr.append(" on t.id=d.parid \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append(" and t.out_date between str_to_date('"+s+"','%Y-%m-%d') and str_to_date('"+e+"','%Y-%m-%d') \n ");

		}else{
			sqlStr.append(" and t.out_date between to_date('"+s+"','yyyy-MM-dd') and to_date('"+e+"','yyyy-MM-dd') \n ");
		}
		
		sqlStr.append(" and notin_code is null \n ");
		sqlStr.append(" ) a,drgs.t_mdcgroup b \n ");
		sqlStr.append(" where a.mdc=b.mdc_code and a.ver_code=b.ver_code \n ");
		sqlStr.append(" group by mdc,mdc_desc \n ");
		sqlStr.append(" order by casecount desc \n ");
		sqlStr.append(" ) t \n ");
		List<Object[]> listdis = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return listdis;
	}

	@Override
	public List<Object[]> getDrgDataInMdc(String s, String e, String mdcCode) {
		
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select t.*,rownum rowindex from (  \n ");
		sqlStr.append(" select pardrg_code,drg_desc,count(1) casecount from(  \n ");
		sqlStr.append(" select   \n ");
		sqlStr.append(" d.* from drgs.T_MEDICALRECORD t  \n ");
		sqlStr.append(" left join drgs.t_patientdrg d  \n ");
		sqlStr.append(" on t.id=d.parid  \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		sqlStr.append(" and d.mdc='"+mdcCode+"'  \n ");
		sqlStr.append(" ) a left join drgs.t_drgs b  \n ");
		sqlStr.append(" on a.pardrg_code=b.drg_code and a.ver_code = b.ver_code  \n ");
		sqlStr.append(" group by pardrg_code,drg_desc \n ");
		sqlStr.append(" order by casecount desc  \n ");
		sqlStr.append(" ) t  \n ");
		List<Object[]> listdrgdis = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return listdrgdis;
	}
	@Override
	public List<Object[]> getOtherCase(String s, String e) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select notin_code,  \n ");
		sqlStr.append(" sum(casecount) casecount \n ");
		sqlStr.append(" from( \n ");
		sqlStr.append(" select  \n ");
		sqlStr.append(" case when notin_code like '%QY%' then 'QY' else '0000' end notin_code, \n ");
		sqlStr.append(" count(*) casecount \n ");
		sqlStr.append(" from drgs.T_MEDICALRECORD t \n ");
		sqlStr.append(" left join drgs.t_patientdrg d \n ");
		sqlStr.append(" on t.id=d.parid \n ");
		sqlStr.append(" where 1=1 ");
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		sqlStr.append(" and pardrg_code is null \n ");
		sqlStr.append(" and notin_code is not null and t.in_days<=60 \n ");
		sqlStr.append(" group by notin_code \n ");
		sqlStr.append(" ) \n ");
		sqlStr.append(" group by notin_code \n ");
		List<Object[]> listOth = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return listOth;
	}

	@Override
	public List<Object[]> getDepCaseData(String s, String e) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select a.*,b.totalcnt from ( \n ");
		sqlStr.append(" select count(1) casecnt, out_dep_code depcode, out_dep depdesc \n ");
		sqlStr.append(" from ( \n ");
		sqlStr.append(" select out_dep_code, out_dep from DRGS.T_PATIENTDRG t \n ");
		sqlStr.append(" left join DRGS.t_medicalrecord d \n ");
		sqlStr.append(" on t.parid=d.id \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(d.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(d.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		sqlStr.append(" and pardrg_code is null \n ");
		sqlStr.append(" and notin_code is not null and d.in_days<=60 \n ");
		sqlStr.append(" ) group by out_dep_code, out_dep \n ");
		sqlStr.append(" ) a left join ( \n ");
		sqlStr.append(" select out_dep_code depcode, count(1) totalcnt from DRGS.t_medicalrecord t \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ s +"' and '"+ e +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ s +"' and '"+ e +"'");
		}
		sqlStr.append(" group by out_dep_code \n ");
		sqlStr.append(" ) b  \n ");
		sqlStr.append(" on a.depcode=b.depcode \n ");
		
		List<Object[]> listdep = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return listdep;
	}

/**
 * 
 *  @Description：获取病人基本信息
 *  @Author zouyiyu  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getPatientInfo(java.lang.String)
 */
	@Override
	public Map<String,Object> getPatientInfo(String patientId) { 
		String sql = "select BS.pmainindex,BS.P00030000,bs.P00060000 ,bs.P00040000 ,BS.P00050000 ,to_char(BS.P00080000,'yyyy-mm-dd') P00080000,  \n"+
				"BS.P00090000 ,((case when bs.P00090001 is null then 0 else bs.P00090001 end)*30+(case when bs.P00090002 is null then 0 else bs.P00090002 end))    \n"+ 
				"as newborn_days,BS.P00110000 ,BS.P00120000 , \n"+
				"to_char(I.P00250000,'yyyy-mm-dd') P00250000,I.P00260000 P00260000,I.P00270000 ,to_char(I.P00290000,'yyyy-mm-dd') P00290000,I.P00300000 ,\n"+
				"I.P00310000 AS OUT_WARD,I.P00280000 AS TRANS_DEP,I.P00600900 AS CODER,BS.P00010001 AS Organization_code, \n"+
				"BS.P00020001 ,BS.P00070001 ,BS.P00180000 ,I.P00230001 ,BS.P00100000 , \n"+
				"I.P00620001 ,I.P00320000 ,I.P00600100 ,I.P00600200 ,I.P00600300 , \n"+
				"I.P00600400 ,I.P00600500 ,I.P00600600 ,I.P00600800 ,I.P00800000 , \n"+
				"I.P00810000 ,A.P90120201 ,BS.P00130100 ,BS.P00130200 ,BS.P00130300 , \n"+
				"BS.P00140100 ,BS.P00140200 ,BS.P00150000 ,BS.P00160000 ,BS.P00170000 , \n"+
				"BS.P00190100 ,BS.P00190200 ,BS.P00190300 ,BS.P00200100 , \n"+
				"BS.P00200200 ,BS.P00200300 ,BS.P00210000 ,BS.P00210100 , \n"+
				"BS.P00210200 ,BS.P00190600 ,BS.P00220000 ,BS.P00220100 , \n"+
				"BS.P00220200 ,BS.P00220300 , BS.P00200500 ,BS.P00190500 , \n"+
				"I.P00400100,I.P00490000 ,I.P00500000 ,I.P00550000 , \n"+
				"I.P00550101||'-'||I.P00550102||'-'||I.P00550103||'周' as follow_time,I.P00510000,I.P00520000 , \n"+
				"I.P00530000 ,I.P00570001 ,I.P00580001 ,I.P00610001 ,I.P00610100 , \n"+
				"I.P00610200 ,to_char(I.P00610300,'yyyy-mm-dd') as P00610300 ,I.P00630001 ,I.P00630100 ,I.P00640001 , \n"+
				"I.P00640002 ,I.P00640003 ,I.P00650001 ,I.P00650002 , \n"+
				"I.P00650003 ,bs.P00090001 ,bs.P00090002 ,i.P00601000 , \n"+
				"i.P00900000 ,i.P00400101 ,i.P00910000 ,  \n"+
				"i.P00920000 ,i.P00930000 ,to_char(I.P00930001,'yyyy-mm-dd hh24:mi:ss') as P00930001  ,to_char(I.P00930002,'yyyy-mm-dd hh24:mi:ss') as P00930002 ,i.P00620100 ,  \n"+
				"i.P00620200 ,i.P00950000 ,i.P00950001 ,i.P00950002 ,i.P00960000 ,  \n"+
				"i.P00960001 ,i.P00940001,i.P00940002,bs.P00010000,to_char(i.P00250000,'yyyy') as in_year,    \n"+
				"to_char(i.P00250000,'mm') as in_month,to_char(i.P00250000,'dd') as in_day,to_char(i.P00250000,'hh24') as in_hour,to_char(i.P00290000,'yyyy') as out_year,   \n"+
				"to_char(i.P00290000,'mm') as out_month,to_char(i.P00290000,'dd') as out_day,to_char(i.P00290000,'hh24') as out_hour,i.P00900001 as outReasonCode,   \n"+
				"i.P00400401 ,to_char(i.P00610300,'yyyy') qc_year,to_char(i.P00610300,'mm') qc_month,to_char(i.P00610300,'dd') QUALITY_DAY   \n"+
				"FROM  drgs.mr_baseinfo bs LEFT JOIN drgs.MR_IPINFO I ON BS.PMAININDEX=I.PMAININDEX  \n"+
				"LEFT JOIN drgs.MR_APPENDPAGE A ON BS.PMAININDEX=A.PMAININDEX \n"+
				"where BS.pmainindex='"+patientId+"'";
		Map<String,Object> pMap=jdbcTemplateObj.queryForMap(sql);
		return pMap;
	}
/**
 * 
 *  @Description：获取手术信息
 *  @Author zouyiyu 
 *  @see com.dhc
DRGsHosp.application.YiZhengHomeApplication#getOperation(java.lang.String)
 */
	@Override
	public Map<String,Object> getOperation(String patientId,String patientversion) {
		String sql = "select op.PO0101 as oper_m_code,to_char(op.PO0200,'yyyy-mm-dd') as oper_m_date,op.PO0301 as oper_m_level,op.PO0100 as oper_m_desc, \n"
				+ "op.PO0400 as oper_m_doc,op.PO0500 as oper_m_assi1,op.PO0600 as oper_m_assi2,op.PO0801 as oper_m_cure_level, \n"
				+ "op.PO0700 as oper_m_anaes_way,op.PO0900 as oper_m_anaes_doc \n" 
				+ "from drgs.mr_operation op where op.pmainindex='" + patientId +"' and op.potype='M' ";
		if(patientversion.equals("BJ")){
			sql+= "\n and op.PO1400 ='1' ";
		}
		try {
		//防止数据问题，有多个主手术问题，随机取一条
		List<Map<String, Object>>	listMap=jdbcTemplateObj.queryForList(sql);
		if(null != listMap && listMap.size()>0) {
			Map<String, Object> map=listMap.get(0);
			return map;
		}else {
			return new HashMap<>() ;
		}
		} catch (EmptyResultDataAccessException e) {  // 没有数据时捕获异常返回空对象
			return new HashMap<>() ;
		}
	}
/**
 * 
 *  @Description：获取其他手术
 *  @Author zouyiyu 
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getOtherOperation(java.lang.String)
 */
	@Override
	public List<Map<String,Object>> getOtherOperation(String patientId,String patientversion) {
		String sql="select op.PO0101 as oper_o_code,to_char(op.PO0200,'yyyy-mm-dd') as oper_o_date,op.PO0301 as oper_o_level,op.PO0100 as oper_o_desc, \n"
				+ "op.PO0400 as oper_o_doc,op.PO0500 as oper_o_assi1,op.PO0600 as oper_o_assi2,op.PO0801 as oper_o_cure_level, \n"
				+ "op.PO0700 as oper_o_anaes_way,op.PO0900 as oper_o_anaes_doc \n" 
				+ "from drgs.mr_operation op where op.pmainindex='" + patientId +"' and op.potype='O' ";
		if(patientversion.equals("BJ")){
			sql+= "\n and op.PO1400 ='1' ";
		}
		sql += " order by op.poindex ";
		List<Map<String,Object>> olo = null ;
		try {
			olo=jdbcTemplateObj.queryForList(sql);
		} catch (EmptyResultDataAccessException e) { // 没有数据时捕获异常返回空对象
//			e.printStackTrace();
			return new ArrayList<>() ;
		}
		return olo;
	}
	/**
	 * 
	 *  @Description：获取主要操作(BJ版)
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getOperate(java.lang.String)
	 */
	public List<Object[]> getOperate(String patientId) {
		String sql="select op.PO0101 as oper_o_code,to_char(op.PO0200,'yyyy-mm-dd') as oper_o_date,op.PO0301 as oper_o_level,op.PO0100 as oper_o_desc, \n"
				+ "op.PO0400 as oper_o_doc,op.PO0500 as oper_o_assi1,op.PO0600 as oper_o_assi2,op.PO0801 as oper_o_cure_level, \n"
				+ "op.PO0700 as oper_o_anaes_way,op.PO0900 as oper_o_anaes_doc \n" 
				+ "from drgs.mr_operation op where op.pmainindex='" + patientId +"' and op.potype='M' and op.PO1400='0'";
		List<Object[]>  olo = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return olo;
	}
	/**
	 * 
	 *  @Description：获取其他操作(BJ版)
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getOtherOperate(java.lang.String)
	 */
	public List<Object[]> getOtherOperate(String patientId) {
		String sql="select op.PO0101 as oper_o_code,to_char(op.PO0200,'yyyy-mm-dd') as oper_o_date,op.PO0301 as oper_o_level,op.PO0100 as oper_o_desc, \n"
				+ "op.PO0400 as oper_o_doc,op.PO0500 as oper_o_assi1,op.PO0600 as oper_o_assi2,op.PO0801 as oper_o_cure_level, \n"
				+ "op.PO0700 as oper_o_anaes_way,op.PO0900 as oper_o_anaes_doc \n" 
				+ "from drgs.mr_operation op where op.pmainindex='" + patientId +"' and op.potype='O' and op.PO1400='0' order by op.poindex";
		List<Object[]>  olo = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return olo;
	}
/**
 * 
 *  @Description：获取主要诊断
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getmainDiagnosis(java.lang.String)
 */
	@Override
	public List<Object[]> getmainDiagnosis(String patientId,String patientversion ) {
		String sql="select d.PD0100 as dia_m_desc,d.PD0101 as dia_m_code,d.PD0201 as dia_m_condition  \n";
				if(patientversion.equals("BJ")){
					sql+=",d.PD0202 as cur_effect  \n";
				}
				sql+= "from drgs.mr_diagnose d where d.pmainindex='"+patientId+"'and d.pdtype='M'";
		List<Object[]>  dlo = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return dlo;
	}
/**
 * 
 *  @Description：获取其他诊断
 *  @Author ZouYiyu  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getDiagnosis(java.lang.String)
 */
	@Override
	public List<Object[]> getDiagnosis(String patientId,String patientversion) {
		String sql="select d.PD0100 as dia_o_desc,d.PD0101 as dia_o_code,d.PD0201 as dia_o_condition  \n";
				if(patientversion.equals("BJ")){
					sql+=",d.PD0202 as cur_effect  \n";
				}
				sql+= "from drgs.mr_diagnose d where d.pmainindex='"+patientId+"'and d.pdtype='O' order by d.pdindex";
		List<Object[]>  dlo = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return dlo;
	}
 
	@Override
	public List<Object[]> getCaseQYData(String startDate, String endDate,String notincode) {
		StringBuilder sqlStr = new StringBuilder();
		sqlStr.append(" select notin_code, casecount, rownum rowindex \n ");
		sqlStr.append(" from( \n ");
		sqlStr.append(" select notin_code, count(1) casecount \n ");
		sqlStr.append(" from drgs.T_MEDICALRECORD t \n ");
		sqlStr.append(" left join drgs.t_patientdrg d \n ");
		sqlStr.append(" on t.id=d.parid \n ");
		sqlStr.append(" where 1=1 ");
		
		if("MYSQL".equals(ConstantEnum.DBTYPE)){
			sqlStr.append( " and date_format(t.out_date,'%Y-%m-%d') between '"+ startDate +"' and '"+ endDate +"'");
		}else{
			sqlStr.append( " and to_char(t.out_date,'yyyy-mm-dd') between '"+ startDate +"' and '"+ endDate +"'");
		}
		
		sqlStr.append(" and pardrg_code is null \n ");
		sqlStr.append(" and notin_code like '%QY%' \n ");
		sqlStr.append(" group by notin_code \n ");
		sqlStr.append(" order by casecount desc \n ");
		sqlStr.append(" ) \n ");
		List<Object[]>  notinl = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return notinl;
	}

	@Override
	public List<Object[]> getDepGrpTalRegionDrgsData(IndexCal queryVo) {
		StringBuilder sqlStr = new StringBuilder();
		
		sqlStr.append("select r.rov_drg_cnt,r.rov_cmi,r.rov_avg_days,null time_eff,r.rov_avg_fee,null fee_eff,null WeightPerDoctor,  \n ");
		sqlStr.append("null WeightPerBedDays,r.rov_dead_ratio_r1,r.rov_dead_ratio_r2,null InRatio \n ");
		// 注释报错
//		sqlStr.append("from drgs.t_regionorgval r where r.rov_ocid = '2014' and r.rov_lot='"+queryVo.getMrLot()+"' \n ");
		List<Object[]>  regioList = getQueryChannelService().createSqlQuery(sqlStr.toString()).list();
		return regioList;
	}
	@Override
	public List<QuotaManage> getDepGrpTalListDrgsData(IndexCal queryVo) {
		// 注释报错
//		List<QuotaManage> ql=getQueryChannelService().createJpqlQuery("select new QuotaManage(_quotaManage.parquotaCode,_quotaManage.manageBenchmark) from QuotaManage _quotaManage where _quotaManage.parDepGroupCode is null and _quotaManage.parDepCode is null and _quotaManage.pardrgCode is null and  _quotaManage.manageYear='"+queryVo.getgetMrLot().substring(0, 4)+"' ").list();
		return null;
	}
	
	@Override
	public List<Object[]> getPatientCost(String patientId) {
		String sql = "select parcost_sub_code, cost from drgs.t_cost where parid='"+patientId+"'";
		List<Object[]> costs = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return costs;
	}

/**
 * 
 *  @Description：执行传过来的sql获取费用明细
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#getCost(java.lang.String)
 */
	@Override
	public List<Object[]> getCost(String patientId) {
		String sql="select tcs.cost_sub_code,tc.PE0200,tcs.cost_sub_desc,tcs.cost_sub_alias "+
				"from drgs.t_costsub tcs left join "+
				"(select c.PE0101,c.PE0200 from drgs.MR_EXPENSE c where c.PMAININDEX = '"+patientId+"') tc "+
				"on tcs.cost_sub_code = tc.PE0101";
		List<Object[]>  cost = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return cost;
	}

	@Override
	public List<Object[]> getCostType() {
		String sql=" select dd.dic_value,dd.dic_desc from drgs.mr_dictionarydata dd where dd.dic_code = 'EXPENSE_TYPE' order by dd.dic_value asc ";	
//		String sql="SELECT COST_CATE_CODE ,COST_CATE_DESC  FROM drgs.t_costcate ORDER BY cost_cate_code ASC";	
		List<Object[]>  costtype = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return costtype;
	}

	@Override
	public List<Object[]> getCostnameInfo(String costsubcode) {
		String sql="select cost_sub_code,cost_sub_desc ,parcost_cate_code from drgs.t_costsub sub where parcost_cate_code='"+costsubcode+"' ORDER BY to_number(COST_SUB_CODE)";
		List<Object[]>  olo = getQueryChannelDRGsService().createSqlQuery(sql.toString()).list();
		return olo;
	}
/**
 * 
 *  @Description：获取查询费用明细的sql
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#findCostSql(java.lang.String)
 */
	@Override
	public String findCostSql(String qcode) {
		/*如果sql不存在的情况下默认执行这条sql*/
		String sqlStr="select * from  drgs.mr_cost";
		String sql="select q.quota_code,dbms_lob.substr(q.select_sql) from drgs.t_quotaselect q where q.quota_code='"+qcode+"'";
		List<Object[]>  list = getQueryChannelService().createSqlQuery(sql.toString()).list();
		if(!list.isEmpty()){
			Object[] obj=list.get(0);
			sqlStr=obj[1]==null?"select * from  drgs.mr_cost":obj[1].toString();
		}
		return sqlStr;
	}
/**
 * 
 *  @Description：获取表格中的字段和字段的注释
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiZhengHomeApplication#findMrCost()
 */
@Override
public List<Object[]> findMrCost() {
	String sql="select tc.COLUMN_NAME,cc.COMMENTS,tc.DATA_TYPE from sys.user_tab_columns tc left join sys.all_col_comments cc on tc.TABLE_NAME = cc.TABLE_NAME and tc.COLUMN_NAME = cc.COLUMN_NAME where tc.TABLE_NAME ='MR_COST' order by tc.COLUMN_ID";
	List<Object[]>  list = getQueryChannelDRGsService().createSqlQuery(sql.toString()).list();
	return list;
}

/**
 * 山西病案首页附页信息（中铁17局）
 */
@Override
public Map<String, Object> getAppendpageInfo(String patientId) {
	String sql = "select BS.pmainindex,ip.P00380001,to_char(ip.P00390000,'yyyy') INSUREYEAR ,to_char(ip.P00390000,'mm') INSUREMONTH , \n"+
			"to_char(ip.P00390000,'dd') INSUREDAY, diag.PD0301, a.P90051001, a.P90052000,ip.P00440001,\n"+
			"ip.P00450001, ip.P00460001, ip.P00470001, a.P00475001, ip.P00480001,a.P90080001, ip.P00330000,  \n"+ 
			"a.P00335000, ip.P00910000, ip.P00490000, ip.P00500000, a.P00336000, a.P90200001, \n"+
			"a.P90210001, a.P90215001, a.P90220001, a.P90230001, a.P90240001, a.P90250001, a.P90260001, \n"+
			"a.P90270001, a.P90280001, a.P90290001, a.P90031001, a.P90032000 \n"+
			"FROM  drgs.mr_baseinfo bs LEFT JOIN drgs.MR_IPINFO ip ON BS.PMAININDEX=ip.PMAININDEX \n"+
			"left join drgs.mr_diagnose diag on ip.PMAININDEX=diag.PMAININDEX and diag.pdtype='M' \n"+
			"LEFT JOIN drgs.MR_APPENDPAGE A ON BS.PMAININDEX=A.PMAININDEX \n"+
			"where BS.pmainindex='"+patientId+"'";
	Map<String,Object> pMap=jdbcTemplateObj.queryForMap(sql);
	return pMap;
}

}
